﻿import postgresql
import os
import sys
from datetime import datetime

__dir__ = os.path.dirname(os.path.abspath(__file__))
path_error = os.path.join(__dir__, "clean_err.log")
sys.stderr = open(path_error, 'a')

if len(sys.argv)!=2 or (sys.argv[1]!="test" and sys.argv[1]!="production") :
	print ('PLEASE USE ONLY: clean.py test OR clean.py production')
else :
	c_user="palo"
	c_password="palo"
	c_host="sa-serv8"
	c_port=5432
	db_openerp=sys.argv[1]
	db_log="log"

	import postgresql.driver as pg_driver

	conn_db = pg_driver.connect(user = c_user, password = c_password, host = c_host, port = c_port, database = db_openerp)
	conn_log = pg_driver.connect(user = c_user, password = c_password, host = c_host, port = c_port, database = db_log)


#	conn_db = postgresql.open("pq://"+user+":"+password+"@"+server+"/"+db_openerp) - ошибки при запуске назначенного задания от имени palo на sa-pc247, вероятно, в скрипте postgresql.open -->
#	conn_log = postgresql.open("pq://"+user+":"+password+"@"+server+"/"+db_log) - clientparameters.py, которые собирает данные для подключения, в отличие от pg_driver, в котором они даются явно.

	insert_into_log = conn_log.prepare("INSERT INTO oe_1s_clean(s_db, s_table, _statement, s_number, s_comment, s_condition) VALUES ($1, $2, $3, $4, $5, $6)")

	if sys.argv[1]=="production" : 

		# удаляем договоры без подразделений

		s_comment="Договоры без подразделений"
		s_table="account_analytic_account"
		s_condition="x_1s_id IS NOT NULL AND manager_id NOT IN (SELECT resource_resource.user_id FROM resource_resource, hr_employee WHERE hr_employee.department_id IN (SELECT DISTINCT id FROM hr_department WHERE x_1s_id IS NOT NULL) AND hr_employee.resource_id=resource_resource.id)"

		delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
		delete_log=delete()
		insert_into_log(db_openerp, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

	# удаляем сотрудников без договоров

	s_comment="Cотрудники без договоров"
	s_table="hr_employee"
	s_condition="x_1s_sc11305_id IS NOT NULL AND resource_id IN (SELECT DISTINCT id FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account))"

	delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
	delete_log=delete()
	insert_into_log(db_openerp, s_table, delete_log[0], delete_log[1], s_comment, s_condition)


	# удаляем ресурсы "без договоров"

	s_comment="Ресурсы без договоров"
	s_table="resource_resource"
	s_condition="x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)"

	delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
	delete_log=delete()
	insert_into_log(db_openerp, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

	# удаляем подразделения "без договоров"

	s_comment="Подразделения без договоров"
	s_table="hr_department"
	s_condition="x_1s_id IS NOT NULL AND id NOT IN (SELECT DISTINCT department_id FROM hr_employee WHERE x_1s_sc11305_id IS NOT NULL AND resource_id NOT IN (SELECT DISTINCT id FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)))"

	delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
	delete_log=delete()
	insert_into_log(db_openerp, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

	# удаляем пользователей "без договоров"

	s_comment="Пользователи без договоров"
	s_table="res_users"
	s_condition="x_1s_sc11305_id IS NOT NULL AND id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)"

	delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
	delete_log=delete()
	insert_into_log(db_openerp, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

	# удаляем контрагентов-пользователей "без пользователей"

	s_comment="Контрагенты-пользователи без пользователей"
	s_table="res_partner"
	s_condition="x_1s_sc11305_id IS NOT NULL AND id NOT IN (SELECT DISTINCT partner_id FROM res_users WHERE x_1s_sc11305_id IS NOT NULL)"

	delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
	delete_log=delete()
	insert_into_log(db_openerp, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

	if sys.argv[1]=="production" : 

		# удаляем контрагентов-контактов "без договоров"

		s_comment="Контрагенты-контакты без договоров"
		s_table="res_partner"
		s_condition="x_1s_id is not null AND id NOT IN (SELECT DISTINCT partner_id FROM account_analytic_account WHERE x_1s_id IS NOT NULL)"

		delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
		delete_log=delete()
		insert_into_log(db_openerp, s_table, delete_log[0], delete_log[1], s_comment, s_condition)


	# удаляем контрагентов-партнеров без контактов
	
	s_comment="Контрагенты-партнеры без контактов"
	s_table="res_partner"
	s_condition="x_1s_name is not null AND id NOT IN (SELECT DISTINCT parent_id FROM res_partner WHERE x_1s_id IS NOT NULL)"

	delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
	delete_log=delete()
	insert_into_log(db_openerp, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

# закрываем подключения

conn_db.close()
conn_log.close()

sys.stderr.write('\n\n'+str(datetime.now())+'  INFO : Finished execution of job clean with status: Completed successfully')

sys.stderr.close()
sys.stderr = sys.__stderr__